package com.gpzx.zx.utlis;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import com.gpzx.zx.module.home.frame.model.GpNewsInfo;
import com.gpzx.zx.module.home.frame.model.OrgInfo;

import org.jetbrains.annotations.NotNull;
import org.jetbrains.annotations.Nullable;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 作者:吴奎庆
 * <p>
 * 时间:2020/6/5
 * <p>
 * 用途: sql3 数据库操作
 */


public class GpDbUtlis extends SQLiteOpenHelper {
    private static String DB_PATH = "data/data/com.gpzx.zx/databases/";
    private static String DB_NAME = "gp";
    String path = DB_PATH + DB_NAME;
    private SQLiteDatabase dbObj;
    public final Context context;

    public GpDbUtlis(Context context) {
        super(context, DB_NAME, null, 3);
        this.context = context;
        try {
            if (!checkDB()) copyDB();
            dbObj = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READWRITE);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 检测数据库是否存在
     *
     * @return
     */
    private boolean checkDB() {

        SQLiteDatabase checkDB = null;
        try {
            checkDB = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
            if (checkDB != null) {
            } else {
                return false;
            }

        } catch (SQLiteException e) {
            e.printStackTrace();
        }
        if (checkDB != null) {
            checkDB.close();
        }
        return checkDB != null ? true : false;
    }


    /**
     * 见assets目录下的文件拷贝到sd上
     *
     * @return 存储数据库的地址
     */

    public void copyDB() throws IOException {
        InputStream ip = context.getAssets().open(DB_NAME + ".sqlite3");
        File dir = new File(DB_PATH);
        if (!dir.exists() || !dir.isDirectory()) {
            dir.mkdir();
        }
        File file = new File(dir, DB_NAME);
        if (!file.exists()) {
            file.createNewFile();
        }
        try {
            OutputStream output = new FileOutputStream(file);
            byte[] buffer = new byte[1024];
            int length;
            while ((length = ip.read(buffer)) > 0) {
                output.write(buffer, 0, length);
            }
            output.flush();
            output.close();
            ip.close();
        } catch (Exception e) {
            ip.close();
        }

    }

    public SQLiteDatabase getDatabase() {
        String myPath = DB_PATH + DB_NAME;
        dbObj = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
        return dbObj;
    }

    public void openDB() {
        try {
            if (dbObj != null && !dbObj.isOpen()) {
                String myPath = DB_PATH + DB_NAME;
                dbObj = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
            }
        } catch (Exception e) {

        }

    }

    @Override
    public synchronized void close() {
        if (dbObj != null)
            dbObj.close();
        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    /**
     * 获取顶部数据
     *
     * @return
     */

    public List<GpNewsInfo> getGpTopNews() {
        openDB();
        List<GpNewsInfo> infos = new ArrayList<>();
        String sql = "SELECT * FROM gp_top_news";
        Cursor cursor = dbObj.rawQuery(sql, null);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            GpNewsInfo info = new GpNewsInfo();
            String id = cursor.getString(cursor.getColumnIndex("id"));
            String gp_title = cursor.getString(cursor.getColumnIndex("gp_title"));
            String gp_icon = cursor.getString(cursor.getColumnIndex("gp_icon"));
            String gp_type = cursor.getString(cursor.getColumnIndex("gp_type"));
            String gp_detail = cursor.getString(cursor.getColumnIndex("gp_detail"));
            info.setId(id);
            info.setGp_title(gp_title);
            info.setGp_icon(gp_icon);
            info.setGp_type(gp_type);
            info.setGp_detail(gp_detail);
            infos.add(info);

        }
        return infos;
    }


    public GpNewsInfo getGpTopNewsById(String newsId) {
        openDB();


        String sql = "SELECT * FROM gp_top_news where id= ?";
        String[] selectionArgs = new String[]{newsId};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);
        GpNewsInfo info = new GpNewsInfo();
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            String id = cursor.getString(cursor.getColumnIndex("id"));
            String gp_title = cursor.getString(cursor.getColumnIndex("gp_title"));
            String gp_icon = cursor.getString(cursor.getColumnIndex("gp_icon"));
            String gp_type = cursor.getString(cursor.getColumnIndex("gp_type"));
            String gp_detail = cursor.getString(cursor.getColumnIndex("gp_detail"));
            info.setId(id);
            info.setGp_title(gp_title);
            info.setGp_icon(gp_icon);
            info.setGp_type(gp_type);
            info.setGp_detail(gp_detail);
        }
        return info;
    }

    /**
     * 获取首页资讯
     *
     * @return
     */
    public List<GpNewsInfo> getGpNews() {
        openDB();
        List<GpNewsInfo> infos = new ArrayList<>();
        String sql = "SELECT * FROM gp_common_news";
        Cursor cursor = dbObj.rawQuery(sql, null);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            GpNewsInfo info = new GpNewsInfo();
            String id = cursor.getString(cursor.getColumnIndex("id"));
            String gp_title = cursor.getString(cursor.getColumnIndex("gp_title"));
            String gp_icon = cursor.getString(cursor.getColumnIndex("gp_icon"));
            String gp_type = cursor.getString(cursor.getColumnIndex("gp_type"));
            String gp_detail = cursor.getString(cursor.getColumnIndex("gp_detail"));
            String gp_desc = cursor.getString(cursor.getColumnIndex("gp_desc"));
            String gp_time = cursor.getString(cursor.getColumnIndex("gp_time"));
            info.setId(id);
            info.setGp_title(gp_title);
            info.setGp_icon(gp_icon);
            info.setGp_type(gp_type);
            info.setGp_detail(gp_detail);
            info.setGp_time(gp_time);
            info.setGp_desc(gp_desc);
            infos.add(info);
        }
        return infos;
    }

    /**
     * 获取首页资讯
     *
     * @return
     */
    public List<GpNewsInfo> getGpNews(String type) {
        openDB();
        List<GpNewsInfo> infos = new ArrayList<>();
        String sql = "SELECT * FROM gp_common_news where gp_type=?";
        String[] selectionArgs = new String[]{type};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);

        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            GpNewsInfo info = new GpNewsInfo();
            String id = cursor.getString(cursor.getColumnIndex("id"));
            String gp_title = cursor.getString(cursor.getColumnIndex("gp_title"));
            String gp_icon = cursor.getString(cursor.getColumnIndex("gp_icon"));
            String gp_type = cursor.getString(cursor.getColumnIndex("gp_type"));
            String gp_detail = cursor.getString(cursor.getColumnIndex("gp_detail"));
            String gp_desc = cursor.getString(cursor.getColumnIndex("gp_desc"));
            String gp_time = cursor.getString(cursor.getColumnIndex("gp_time"));
            info.setId(id);
            info.setGp_title(gp_title);
            info.setGp_icon(gp_icon);
            info.setGp_type(gp_type);
            info.setGp_detail(gp_detail);
            info.setGp_time(gp_time);
            info.setGp_desc(gp_desc);
            infos.add(info);
        }
        return infos;
    }

    /**
     * 根据id 获取新闻
     *
     * @param newId 新闻ID
     * @return
     */
    public GpNewsInfo getGpNewsById(String newId) {

        openDB();
        String sql = "SELECT * FROM gp_common_news where id=?";
        String[] selectionArgs = new String[]{newId};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        GpNewsInfo info = new GpNewsInfo();
        while (cursor.moveToNext()) {
            String id = cursor.getString(cursor.getColumnIndex("id"));
            String gp_title = cursor.getString(cursor.getColumnIndex("gp_title"));
            String gp_icon = cursor.getString(cursor.getColumnIndex("gp_icon"));
            String gp_type = cursor.getString(cursor.getColumnIndex("gp_type"));
            String gp_detail = cursor.getString(cursor.getColumnIndex("gp_detail"));
            String gp_desc = cursor.getString(cursor.getColumnIndex("gp_desc"));
            String gp_time = cursor.getString(cursor.getColumnIndex("gp_time"));
            info.setId(id);
            info.setGp_title(gp_title);
            info.setGp_icon(gp_icon);
            info.setGp_type(gp_type);
            info.setGp_detail(gp_detail);
            info.setGp_time(gp_time);
            info.setGp_desc(gp_desc);

        }
        return info;
    }


    /**
     * 获取指定 type的机构列表
     *
     * @return
     */
    public List<OrgInfo> getOrgTypeList(String type) {
        openDB();
        List<OrgInfo> infos = new ArrayList<>();
        String sql = "SELECT * FROM qh_organization_news where o_type=?";
        String[] selectionArgs = new String[]{type};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);

        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            OrgInfo info = new OrgInfo();
            String id = cursor.getString(cursor.getColumnIndex("id"));
            String o_name = cursor.getString(cursor.getColumnIndex("o_name"));
            String o_icon = cursor.getString(cursor.getColumnIndex("o_icon"));
            String o_type = cursor.getString(cursor.getColumnIndex("o_type"));
            String o_desc = cursor.getString(cursor.getColumnIndex("o_desc"));
//            String o_news_title = cursor.getString(cursor.getColumnIndex("o_news_title"));
//            String o_news_icon = cursor.getString(cursor.getColumnIndex("o_news_icon"));
//            String o_news_detail = cursor.getString(cursor.getColumnIndex("o_news_detail"));
//            String o_news_time = cursor.getString(cursor.getColumnIndex("o_news_time"));
            info.setId(id);
            info.setO_name(o_name);
            info.setO_desc(o_desc);
            info.setO_icon(o_icon);
            info.setO_type(o_type);

//            info.setO_news_time(o_news_time);
//            info.setO_news_detail(o_news_detail);
//
//            info.setO_news_icon(o_news_icon);
//            info.setO_news_title(o_news_title);
            infos.add(info);
        }
        return infos;
    }

    /**
     * 获取指定 type的机构列表
     *
     * @return
     */
    public List<OrgInfo> getOrgTypeByName(String name) {
        openDB();
        List<OrgInfo> infos = new ArrayList<>();
        String sql = "SELECT * FROM qh_organization_news where o_name=?";
        String[] selectionArgs = new String[]{name};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);

        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            OrgInfo info = new OrgInfo();
            String id = cursor.getString(cursor.getColumnIndex("id"));
            String o_name = cursor.getString(cursor.getColumnIndex("o_name"));
            String o_icon = cursor.getString(cursor.getColumnIndex("o_icon"));
            String o_type = cursor.getString(cursor.getColumnIndex("o_type"));
            String o_desc = cursor.getString(cursor.getColumnIndex("o_desc"));
            String o_news_title = cursor.getString(cursor.getColumnIndex("o_news_title"));
            String o_news_icon = cursor.getString(cursor.getColumnIndex("o_news_icon"));
            String o_news_detail = cursor.getString(cursor.getColumnIndex("o_news_detail"));
            String o_news_time = cursor.getString(cursor.getColumnIndex("o_news_time"));
            info.setId(id);
            info.setO_name(o_name);
            info.setO_desc(o_desc);
            info.setO_icon(o_icon);
            info.setO_type(o_type);

            info.setO_news_time(o_news_time);
            info.setO_news_detail(o_news_detail);

            info.setO_news_icon(o_news_icon);
            info.setO_news_title(o_news_title);
            infos.add(info);
        }
        return infos;
    }


    /**
     * 获取指定 type的机构列表
     *
     * @return
     */
    public OrgInfo getOrgById(String orgId) {
        openDB();

        String sql = "SELECT * FROM qh_organization_news where id=?";
        String[] selectionArgs = new String[]{orgId};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);
        OrgInfo info = new OrgInfo();
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {

            String id = cursor.getString(cursor.getColumnIndex("id"));
            String o_name = cursor.getString(cursor.getColumnIndex("o_name"));
            String o_icon = cursor.getString(cursor.getColumnIndex("o_icon"));
            String o_type = cursor.getString(cursor.getColumnIndex("o_type"));
            String o_desc = cursor.getString(cursor.getColumnIndex("o_desc"));
            String o_news_title = cursor.getString(cursor.getColumnIndex("o_news_title"));
            String o_news_icon = cursor.getString(cursor.getColumnIndex("o_news_icon"));
            String o_news_detail = cursor.getString(cursor.getColumnIndex("o_news_detail"));
            String o_news_time = cursor.getString(cursor.getColumnIndex("o_news_time"));
            info.setId(id);
            info.setO_name(o_name);
            info.setO_desc(o_desc);
            info.setO_icon(o_icon);
            info.setO_type(o_type);

            info.setO_news_time(o_news_time);
            info.setO_news_detail(o_news_detail);

            info.setO_news_icon(o_news_icon);
            info.setO_news_title(o_news_title);

        }
        return info;
    }
    /**
     * 获取机构列表
     *
     * @return
     */
    public List<OrgInfo> getOrgTypes() {
        openDB();
        List<OrgInfo> infos = new ArrayList<>();
        String sql = "SELECT * FROM qh_organization_news";
        Cursor cursor = dbObj.rawQuery(sql, null);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            OrgInfo info = new OrgInfo();

            String id = cursor.getString(cursor.getColumnIndex("id"));
            String o_name = cursor.getString(cursor.getColumnIndex("o_name"));
            String o_icon = cursor.getString(cursor.getColumnIndex("o_icon"));
            String o_type = cursor.getString(cursor.getColumnIndex("o_type"));
            String o_desc = cursor.getString(cursor.getColumnIndex("o_desc"));
            String o_news_title = cursor.getString(cursor.getColumnIndex("o_news_title"));
            String o_news_icon = cursor.getString(cursor.getColumnIndex("o_news_icon"));
            String o_news_detail = cursor.getString(cursor.getColumnIndex("o_news_detail"));
            String o_news_time = cursor.getString(cursor.getColumnIndex("o_news_time"));
            info.setId(id);
            info.setO_name(o_name);
            info.setO_desc(o_desc);
            info.setO_icon(o_icon);
            info.setO_type(o_type);

            info.setO_news_time(o_news_time);
            info.setO_news_detail(o_news_detail);

            info.setO_news_icon(o_news_icon);
            info.setO_news_title(o_news_title);
            infos.add(info);
        }
        return infos;
    }


    @NotNull
    public List<GpNewsInfo> getZxNews(@Nullable String type) {
        openDB();
        List<GpNewsInfo> infos = new ArrayList<>();
        String sql = "SELECT * FROM gp_news where gp_type=?";
        String[] selectionArgs = new String[]{type};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);

        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            GpNewsInfo info = new GpNewsInfo();
            String id = cursor.getString(cursor.getColumnIndex("id"));
            String gp_title = cursor.getString(cursor.getColumnIndex("gp_title"));
            String gp_icon = cursor.getString(cursor.getColumnIndex("gp_icon"));
            String gp_type = cursor.getString(cursor.getColumnIndex("gp_type"));
            String gp_detail = cursor.getString(cursor.getColumnIndex("gp_detail"));
            String gp_desc = cursor.getString(cursor.getColumnIndex("gp_desc"));
            String gp_time = cursor.getString(cursor.getColumnIndex("gp_time"));
            info.setId(id);
            info.setGp_title(gp_title);
            info.setGp_icon(gp_icon);
            info.setGp_type(gp_type);
            info.setGp_detail(gp_detail);
            info.setGp_time(gp_time);
            info.setGp_desc(gp_desc);
            infos.add(info);
        }
        return infos;
    }

    @NotNull
    public GpNewsInfo getZxNewsById(@Nullable String newsID) {
        openDB();

        String sql = "SELECT * FROM gp_news where id=?";
        String[] selectionArgs = new String[]{newsID};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);
        GpNewsInfo info = new GpNewsInfo();
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {

            String id = cursor.getString(cursor.getColumnIndex("id"));
            String gp_title = cursor.getString(cursor.getColumnIndex("gp_title"));
            String gp_icon = cursor.getString(cursor.getColumnIndex("gp_icon"));
            String gp_type = cursor.getString(cursor.getColumnIndex("gp_type"));
            String gp_detail = cursor.getString(cursor.getColumnIndex("gp_detail"));
            String gp_desc = cursor.getString(cursor.getColumnIndex("gp_desc"));
            String gp_time = cursor.getString(cursor.getColumnIndex("gp_time"));
            info.setId(id);
            info.setGp_title(gp_title);
            info.setGp_icon(gp_icon);
            info.setGp_type(gp_type);
            info.setGp_detail(gp_detail);
            info.setGp_time(gp_time);
            info.setGp_desc(gp_desc);

        }
        return info;
    }
}
